package code;

import java.io.InputStream;
import java.sql.*;
import java.util.Calendar;
import java.util.Properties;
import java.util.Vector;

public class MYSQL
{
    static private Connection conn = null;

    static public void getConnection() throws Exception
    {
        if (conn != null)
        {
            System.out.println("数据库已连接");
            return ;
        }
        //1、读取配置文件中的4个基本信息
        InputStream is = MYSQL.class.getClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2、加载驱动
        Class.forName(driverClass);

        //3、获取连接
        conn = DriverManager.getConnection(url, user, password);
        System.out.println("连接数据库" + url + "成功");
    }

    static public void endConnection() throws Exception
    {
        if (conn == null)
        {
            System.out.println("数据库未连接");
            return ;
        }
        conn.close();
        conn = null;
        System.out.println("数据库断开连接");
    }

    static public void maintain(String sql) throws SQLException
    {
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();
    }

    public static int query_uid(String sql) throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        int uid = 0;
        if (rs.next())
            uid = rs.getInt("uid");
        rs.close();
        stmt.close();
        return uid;
    }

    public static int query_login(int uid, String password_hash) throws SQLException
    {
        int res = -2;
        if (uid <= 200000000) // 普通用户
        {
            uid -= 100000000;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * \n" +
                    "from user \n" +
                    "where UID = " + uid
                    );
            if (rs.next())
            {
                String password = rs.getString("password");
                if (password.equals(password_hash))
                    res = 1;
                else
                    res = 0;
            }
            else
                res = -1;
            stmt.close();
            rs.close();
        }
        else if (uid <= 300000000) // 商家
        {
            uid -= 200000000;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * \n" +
                    "from enterprise \n" +
                    "where UID = " + uid
            );
            if (rs.next())
            {
                String password = rs.getString("password");
                if (password.equals(password_hash))
                    res = 2;
                else
                    res = 0;
            }
            else
                res = -1;
            stmt.close();
            rs.close();
        }
        else // 食堂管理员
        {
            uid -= 300000000;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * \n" +
                    "from administrator \n" +
                    "where UID = " + uid
            );
            if (rs.next())
            {
                String password = rs.getString("password");
                if (password.equals(password_hash))
                    res = 3;
                else
                    res = 0;
            }
            else
                res = -1;
            stmt.close();
            rs.close();
        }
        return res;
    }

    public static String[] query_info(int uid) throws SQLException
    {
        String[] res = new String[4];
        if (uid <= 200000000) // 普通用户
        {
            uid -= 100000000;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * \n" +
                    "from user \n" +
                    "where UID = " + uid
            );
            if (rs.next())
            {
                res[0] = "姓名：" + rs.getString("name");
                res[1] = "联系方式：" + rs.getString("phone");
                res[2] = "密码：******";
            }

            stmt.close();
            rs.close();
            return res;
        }
        else if (uid <= 300000000) // 商家
        {
            uid -= 200000000;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * \n" +
                    "from enterprise \n" +
                    "where UID = " + uid
            );
            if (rs.next())
            {
                res[0] = "商家名：" + rs.getString("name");
                res[1] = "联系方式：" + rs.getString("phone");
                res[2] = "经理：" + rs.getString("manager");
                res[3] = "密码：******";
            }

            stmt.close();
            rs.close();
            return res;
        }
        else // 食堂管理员
        {
            uid -= 300000000;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * \n" +
                    "from administrator \n" +
                    "where UID = " + uid
            );
            if (rs.next())
            {
                res[0] = "姓名：" + rs.getString("name");
                res[1] = "联系方式：" + rs.getString("phone");
                res[2] = "密码：******";
            }
            stmt.close();
            rs.close();
            return res;
        }
    }

    public static String[][] query_admin_canteen(int uid) throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select CID, canteen.name, pos, shop_num \n" +
                "from canteen, administrator \n" +
                "where administrator.UID = " + uid + "\n" +
                "and administrator.UID = canteen.UID"
        );
        while (rs.next())
        {
            String[] temp = new String[4];
            temp[0] = String.valueOf(rs.getInt(1));
            temp[1] = rs.getString(2);
            temp[2] = rs.getString(3);
            temp[3] = String.valueOf(rs.getInt(4));
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][4]);
    }

    public static boolean del_canteen(int cid) throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * \n" +
                "from shop \n" +
                "where CID = " + cid
        );
        if (rs.next())
        {
            stmt.close();
            rs.close();
            return false;
        }
        stmt.close();
        rs.close();
        maintain("delete from canteen where CID = " + cid);
        return true;
    }

    public static boolean exist_canteen_name(String name) throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * \n" +
                "from canteen \n" +
                "where name = " + name
        );
        if (rs.next())
        {
            stmt.close();
            rs.close();
            return true;
        }
        stmt.close();
        rs.close();
        return false;
    }

    public static String[][] query_ep_shop(int uid) throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select SID, shop.name, canteen.name, dish_num \n" +
                "from enterprise, canteen, shop \n" +
                "where enterprise.UID = " + uid + "\n" +
                "and enterprise.UID = shop.UID \n" +
                "and shop.CID = canteen.CID"
        );
        while (rs.next())
        {
            String[] temp = new String[4];
            temp[0] = String.valueOf(rs.getInt(1));
            temp[1] = rs.getString(2);
            temp[2] = rs.getString(3);
            temp[3] = String.valueOf(rs.getInt(4));
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][4]);
    }

    public static boolean del_shop(int sid) throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * \n" +
                "from dish \n" +
                "where SID = " + sid
        );
        if (rs.next())
        {
            stmt.close();
            rs.close();
            return false;
        }
        stmt.close();
        rs.close();
        maintain("delete from shop where SID = " + sid);
        return true;
    }

    public static int getCID(String name) throws SQLException
    {
        int cid = 0;
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select CID \n" +
                "from canteen \n" +
                "where name = " + name
        );
        if (rs.next())
            cid = rs.getInt("CID");
        stmt.close();
        rs.close();
        return cid;
    }

    public static String[] getAllcanteen() throws SQLException
    {
        Vector<String> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select name \n" +
                "from canteen \n"
        );
        while (rs.next())
            res.add(rs.getString("name"));
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()]);
    }

    public static String[][] query_shop_dish(int sid) throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select DID, dish.name, price, sale \n" +
                "from shop, dish \n" +
                "where shop.SID = " + sid + "\n" +
                "and dish.SID = shop.SID "
        );
        while (rs.next())
        {
            String[] temp = new String[4];
            temp[0] = String.valueOf(rs.getInt(1));
            temp[1] = rs.getString(2);
            temp[2] = String.valueOf(rs.getFloat(3));
            temp[3] = String.valueOf(rs.getInt(4));
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][4]);
    }

    public static void del_dish(int did) throws SQLException
    {
        maintain("delete from dish where DID = " + did);
    }

    public static String[][] query_user_addr(int uid) throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select address.AID, address \n" +
                "from address, user_addr \n" +
                "where user_addr.UID = " + uid + "\n" +
                "and user_addr.AID = address.AID "
        );
        while (rs.next())
        {
            String[] temp = new String[2];
            temp[1] = String.valueOf(rs.getInt(1));
            temp[0] = rs.getString(2);
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][2]);
    }


    public static boolean add_user_addr(String address, int uid) throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select address \n" +
                "from address, user_addr \n" +
                "where address = " + address + "\n" +
                "and address.AID = user_addr.AID \n" +
                "and UID = " + uid
        );
        if (rs.next())
        {
            stmt.close();
            rs.close();
            return false;
        }
        rs = stmt.executeQuery("select address \n" +
                "from address \n" +
                "where address = " + address
        );
        if (!rs.next())
        {
            maintain("insert into address(address) values (" + address + ")");
        }
        rs = stmt.executeQuery("select AID \n" +
                "from address \n" +
                "where address = " + address
        );
        rs.next();
        int aid = rs.getInt("AID");
        maintain("insert into user_addr values(" + aid + ", " + uid + ")");
        stmt.close();
        rs.close();
        return true;
    }

    public static void del_user_addr(String address, int uid) throws SQLException
    {
        int aid;
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select AID \n" +
                "from address \n" +
                "where address = " + address
        );
        if (rs.next())
        {
            aid = rs.getInt("AID");
        }
        else
        {
            stmt.close();
            rs.close();
            return;
        }
        maintain("delete from user_addr \n" +
                "where AID = " + aid + "\n" +
                "and UID = " + uid
        );
        stmt.close();
        rs.close();
    }

    public static String[][] query_user_order(int uid) throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select OID, description, price, create_time, status \n" +
                "from orders, user \n" +
                "where orders.UID = " + uid + "\n" +
                "and user.UID = orders.UID \n" +
                "and timestampdiff(day, create_time, now()) <= 7 \n" +
                "order by OID DESC"
        ); // 查找最近7天内订单
        while (rs.next())
        {
            String[] temp = new String[5];
            temp[0] = String.valueOf(rs.getInt(1));
            temp[1] = rs.getString(2);
            temp[2] = String.valueOf(rs.getFloat(3));
            temp[3] = rs.getTimestamp(4).toString();
            temp[4] = rs.getBoolean(5) ? "完成" : "未完成";
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][5]);
    }

    public static String[][] query_incomplete_order(int uid) throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select OID, name, description, price, create_time, status \n" +
                "from orders, shop \n" +
                "where shop.UID = " + uid + "\n" +
                "and shop.SID = orders.SID \n" +
                "and status = 0 \n" +
                "order by OID DESC"
        ); // 查找未完成订单
        while (rs.next())
        {
            String[] temp = new String[6];
            temp[0] = String.valueOf(rs.getInt(1));
            temp[1] = rs.getString(2);
            temp[2] = rs.getString(3);
            temp[3] = String.valueOf(rs.getFloat(4));
            temp[4] = rs.getTimestamp(5).toString();
            temp[5] = rs.getBoolean(6) ? "完成" : "未完成";
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][6]);
    }

    public static boolean is_incomplete(int uid) throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * \n" +
                "from orders, shop \n" +
                "where shop.UID = " + uid + "\n" +
                "and shop.SID = orders.SID \n" +
                "and status = 0 "
        );// 查找未完成订单
        if (rs.next())
        {
            stmt.close();
            rs.close();
            return true;
        }
        stmt.close();
        rs.close();
        return false;
    }

    public static String[][] query_shop_order(int sid) throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select OID, description, price, create_time \n" +
                "from orders, shop \n" +
                "where shop.SID = " + sid + "\n" +
                "and shop.SID = orders.SID \n" +
                "order by OID DESC"
        );
        while (rs.next())
        {
            String[] temp = new String[4];
            temp[0] = String.valueOf(rs.getInt(1));
            temp[1] = rs.getString(2);
            temp[2] = String.valueOf(rs.getFloat(3));
            temp[3] = rs.getTimestamp(4).toString();
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][4]);
    }

    public static String[][] getAllshop() throws SQLException
    {
        Vector<String[]> res = new Vector<>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select SID, name \n" +
                "from shop \n"
        );
        while (rs.next())
        {
            String[] temp = new String[2];
            temp[0] = String.valueOf(rs.getInt("SID"));
            temp[1] = rs.getString("name");
            res.add(temp);
        }
        stmt.close();
        rs.close();
        return res.toArray(new String[res.size()][2]);
    }
}
